package com.jxc.util;

import java.io.File;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javassist.expr.NewArray;

import javax.persistence.Id;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.ListableBeanFactory;
import org.springframework.beans.factory.annotation.Autowired;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import com.jxc.model.po.MemberType;
import com.jxc.model.vo.VMember;
import com.jxc.model.vo.VMemberType;
import com.jxc.model.vo.VRepertory;
import com.jxc.model.vo.VUser;
import com.jxc.service.MemberServiceI;
import com.jxc.service.MemberTypeServiceI;
import com.jxc.service.RepertoryServiceI;
import com.jxc.service.UserServiceI;


//第一行为表头信息 不需要存入数据库

//读取每一行的信息 赋值给对象

//会员编号	名称	会员类别	折扣	手机	生日	消费金额	积分	地址	备注	办卡日期	经销商	所在店铺

/**
 * 步骤：
 * 1、上传份文件
 * 2、分析合法性
 * 3、分析数据
 * 4、设置查询主外键数据
 * 5、赋值
 * 6、保存对象
 * 7、记录失败条数和成功条数
 */


/**
 * 
 * 从excel导入会员信息到数据库
 * 
 * @author PanJing
 *
 */
public class ImportMember {
	
	private File file;	//上传的excel对象
	private HttpServletRequest request;	//需要使用到Session
	private HttpSession session;		//session object
	private MemberServiceI memberService;	//会员服务

	private UserServiceI userService;		//经销商
	
	private RepertoryServiceI repertoryService;	//仓库
	private MemberTypeServiceI memberTypeService;	//会员类型服务
	
	private String values;					//值对应
	private VUser vUser;					//经销商对象
	private List<VMember> list=new ArrayList<VMember>();	//数据容器
	private Sheet sheet;	//excel表对象
	private int success;	//成功条数
	private int fail;		//失败条数
	
	private List<String> repertoryList=new ArrayList<String>();//仓库列表 不重复的
	public List<String> getRepertoryList() {
		return repertoryList;
	}
	private List<ItemBean> beanList=new ArrayList<ItemBean>();	//存放key和value对应的匹配信息
	
	//数据库中存在的数据类型
	private List<String> lA=new ArrayList<String>();
	
	//对excel表进行一个汇总
	private List<String> lB=new ArrayList<String>();
	
	
	//头标准 
	private String [] headA=new String[]{"会员卡号","名称","会员类别","折扣","手机","生日","消费金额","积分","地址","备注","办卡日期","经销商","仓库"};
	
	private String[] headB=null;	//文件头的信息
	
	public List<String> getlA() {
		return lA;
	}
	
	public List<String> getlB() {
		return lB;
	}
	
	public void setvUser(VUser vUser) {
		this.vUser = vUser;
	}
	public VUser getvUser() {
		return vUser;
	}
	
	
	public void setUserService(UserServiceI userService) {
		this.userService = userService;
	}
	
	public void setMemberTypeService(MemberTypeServiceI memberTypeService) {
		this.memberTypeService = memberTypeService;
	}
	
	public void setRepertoryService(RepertoryServiceI repertoryService) {
		this.repertoryService = repertoryService;
	}
	/**
	 * 设置会员服务层，在从Session取出该类的时候 需要设置下
	 * @param memberService
	 */
	public void setMemberService(MemberServiceI memberService) {
		this.memberService = memberService;
	}
	
	/**
	 * 获取成功导入的记录数
	 * @return
	 */
	public int getSuccess() {
		return success;
	}
	
	/**
	 * 获取失败条数
	 * @return
	 */
	public int getFail() {
		return fail;
	}
	
	public void setValues(String values) {
		this.values = values;
	}
	
	public String getValues() {
		return values;
	}
	
	/**
	 * 传入File 对象和Request请求对象
	 * @param file
	 * @param request
	 */
	public ImportMember(File file,HttpServletRequest request,MemberServiceI memberService){
		
		this.file=file;
		this.request=request;
		this.session=request.getSession();
		this.memberService=memberService;
	}
	
	/**
	 * 导入到数据库
	 * @throws Exception 
	 */
	public String doImport() throws Exception{
		
		//检查文件是否合法
		
		Workbook book = Workbook.getWorkbook(this.file);
		Sheet sheet=book.getSheet(0);
		
		this.sheet=sheet;
		
		int colunm=sheet.getColumns();
		int row=sheet.getRows();
		
		if(row==0||colunm==0){
			return ImportInfo.FAIL;
		}
		
		
		//声明表头的容器
		
		headB=new String[colunm];
		
		
		//读取excel的表头
		for(int i=0;i<colunm;i++){
			
			String value=sheet.getCell(i,0).getContents();
			headB[i]=value;
		}

		//会员类别差异数量
		int notSames=getNotSame().size();

		//对已经有的会员类型和数据库的会员类型进行一个汇总 统计出不同的
		
	

		
		// get value begin
		
		//去掉表头
		for(int r=1;r<row;r++){
			
			VMember vMember=new VMember();
			//设置会员编号
			
			
	//"会员卡号","名称","会员类别","折扣","手机","生日","消费金额","积分","地址","备注","办卡日期","经销商","仓库";
			vMember.setSerial(getValue(sheet, "会员卡号", r));
			vMember.setName(getValue(sheet, "姓名", r));
			vMember.setAddress(getValue(sheet, "地址", r));
			vMember.setBirthdayA(getValue(sheet, "生日", r));
			vMember.setRemark(getValue(sheet, "备注", r));
			vMember.setMobile(getValue(sheet, "手机", r));
			
			
			//会可能转换失败,转换失败的时候 数值型数据默认为0
			try{
				vMember.setDiscountNum(Double.valueOf(getValue(sheet, "折扣", r)));
			}catch(Exception e){
				vMember.setDiscountNum(0d);
			}
			
			try{
				vMember.setConsume(Double.valueOf(getValue(sheet, "消费金额", r)));
			}catch(Exception e){
				vMember.setConsume(0d);
			}
			
			try{
				vMember.setPoint(Integer.valueOf(getValue(sheet, "积分", r)));
			}catch(Exception e){
				vMember.setPoint(0);
			}
			
			try{
				DateFormat format = new SimpleDateFormat("dd/MM/yyy"); 
				Date date=format.parse(getValue(sheet, "办卡日期", r));
				vMember.setOpenDate(date);
			
			}catch(Exception e){
				e.printStackTrace();
			}
			//设置经销商
			vMember.setUid(vUser.getId());
			
			//设置会员类别
			//如果不匹配的话 不会进行导入
			String memberType=getValue(sheet, "会员类别", r);
			
			
			if(findByLA(memberType)){
				
				VMemberType type=new VMemberType();
				type.setName(memberType);	//设置类别名称
				type.setUid(vUser.getId());	//设置经销商的id
				
				List<VMemberType> mTypes=memberTypeService.combobox(type);
			
				if(mTypes!=null&&mTypes.size()>0){
					MemberType vType=new MemberType();
					vType.setId(mTypes.get(0).getId());
					vMember.setMemberType(vType);
				}
			}
			
			vMember.setTname(getValue(sheet, "会员类别", r));
			
			//设置所在的店铺
//			VRepertory vRepertory = repertoryService.get(vUser.getReid());
//			vMember.setUid(vRepertory.getUid());
			
			//设置经销商
			vMember.setUid(vUser.getId());
			
			//设置店铺名
		
			String repString=getValue(sheet, "仓库", r);
			
			//放入列表中
			if(repertoryList.indexOf(repString)==-1){
				repertoryList.add(repString);
			}
			
			vMember.setStorename(repString);
			
			list.add(vMember);
		}
		//get value end 
		
		if(notSames==0&&getRepertoryNotSame().size()==0){
			
			//保存到数据库中
			for (VMember v:list) {
				try{
					memberService.save(v);
					//统计成功条数
					this.success++;
					
				}catch(Exception e){
					
					this.fail++;
				}
				
			}
			
			//返回成功的标志
			return ImportInfo.SUCCESS;
		}else {
			
			//将对象存放到session
			request.getSession().setAttribute(ImportInfo.SESSOPM_SIGN, this);
			
			//需要对会员类型和店铺进行匹配
			if(notSames!=0&&getRepertoryNotSame().size()!=0){
				
				return ImportInfo.MEMBER_REPERTORY;
			}
			else if(notSames!=0){
				
				return ImportInfo.NOTSAME;
				
			}else if(getRepertoryNotSame().size()!=0){
				
				return ImportInfo.REPERTORY;
			}
			
			return ImportInfo.FAIL;
			
		}

	}
	
	/**
	 * 获取单元格的值
	 * @param sheet
	 * @param name
	 * @param row
	 * @return
	 */
	private String getValue(Sheet sheet,String name,int row){
		
		
		int result=findByHead(name.trim());
		if(result!=-1){
			return sheet.getCell(result, row).getContents();
		}
		
		return "";
		
	}

	/**
	 * 寻找表头索引
	 * @param name
	 * @return
	 */
	private int findByHead(String name){
		
		for(int i=0;i<headB.length;i++){
			
			if(headB[i].equals(name)){
				return i;
			}
			
		}
		
		return -1;
	}

	
	/**
	 * 对数据库和excel表中的会员类别数据进行一个汇总，找不不同的
	 */
	public List<String> getNotSame(){
		
		List<String> ls=new ArrayList<String>();
		
		//得到当前登录的经销商的会员类别
		VMember vMember=new VMember();
		vMember.setUid(vUser.getUpid());
		
		List<VMember> listA=memberService.combobox(vMember);
		
		//存入map
		for(VMember v:listA){
			if(lA.indexOf(v.getTname())==-1){
				if(v.getName()!=""&&v.getTname()!=null){
					lA.add(v.getTname());
				}
			}
		}
		
		//读取excel表的会员列表信息
		int row=this.sheet.getRows();
		//i=1 去掉表头
		for(int i=1;i<row;i++){
			String value=getValue(this.sheet,"会员类别", i);
			//mapB.put(value,value);
			if(lB.indexOf(value)==-1){
				if(value!="")
				lB.add(value);
			}
		}
		
		//进行比对 找出不同 以mapA为标准，mapA是从数据库中取出来的
		
		for(String s:lB){
			
			if(lA.indexOf(s)==-1){
				ls.add(s);
			}
			
		}
		
		return ls;
	}

	/**
	 * 数据存在差异的时候就调用该方法
	 * @throws Exception
	 */
	public String doSave() throws Exception{
	
		if(values==null)return ImportInfo.FAIL;
		
		
		//将每一项分开
		String [] items=values.split("!");
		for (String string : items) {
			
			//key和values 分离
			String[] ays=string.split(":");
			//如果没有选择匹配就丢弃
			if(ays.length==2){
				ItemBean itemBean=new ItemBean();
				itemBean.setKey(ays[0]);
				itemBean.setValues(ays[1].split(","));
				beanList.add(itemBean);
			}
		}
		
		//匹配、做数据导入
		
		//遍历数据和修改值
		
		for (VMember v:list) {
			if(v.getTname()!=null){
				String sname=findByBean(v.getTname());
				if(sname!=null){
					v.setTname(sname);
					
					//设置会员类别
					if(findByLA(sname)){
						
						VMemberType type=new VMemberType();
						type.setName(sname);	//设置类别名称
						type.setUid(vUser.getId());	//设置经销商的id
						
						List<VMemberType> mTypes=memberTypeService.combobox(type);
					
						if(mTypes!=null&&mTypes.size()>0){
							MemberType vType=new MemberType();
							vType.setId(mTypes.get(0).getId());
							v.setMemberType(vType);
						}
					}
					
					
				}
			}
			
			//开始保存数据到数据库中
			try{
				memberService.save(v);
				this.success++;
			}catch(Exception e){
				this.fail++;
			}
			
		}
		

		
		return ImportInfo.SUCCESS;
	}
	
	/**
	 * 寻找bean
	 * @param name
	 * @return
	 */
	public String findByBean(String name){
		
		for(ItemBean item:beanList){
			
			if(item.find(name)){
				return item.getKey();
			}
			
		}
		return null;
	}
	
	/**
	 * 查找仓库是否存在
	 * @param name
	 * @return
	 */
	public boolean findByRepertory(String name){
	
		VRepertory repertory=new VRepertory();
		repertory.setUid(vUser.getId());
		List<VRepertory> vList=repertoryService.findInConfirm(repertory);
		if(vList!=null){
			
			for (VRepertory vRepertory : vList) {
				
				//System.out.println(vRepertory.getName()+" - "+name);
				if(vRepertory.getName().equals(name)){
					return true;
				}
				
			}
			
		}
		
		return false;
	}
	
	/**
	 * 寻找数据库中的会员类型是否有存在指定的值
	 * @param name
	 * @return
	 */
	public boolean findByLA(String name){
		
		if(name==null||name==""){
			return false;
		}
		
		for(String s:lA){
			if(s.equals(name)){
				return true;
			}
		}
		return false;
	}
	
	/**
	 * 得到不重复的仓库列表
	 * @return
	 */
	public List<String> getRepertorys(){
		
		List<String> rList=new ArrayList<String>();
		
		VRepertory repertory=new VRepertory();
		repertory.setUid(vUser.getId());
		List<VRepertory> vList=repertoryService.findInConfirm(repertory);
		for (VRepertory vRepertory : vList) {
			
			if(rList.indexOf(vRepertory.getName())==-1){
				if(vRepertory.getName()!=null&&vRepertory.getName()!="")
				rList.add(vRepertory.getName());
			}
			
		}
	
		return rList;
	}
	/**
	 * 得到仓库和excel表格的仓库的不同之处
	 * @return
	 */
	public List<String> getRepertoryNotSame(){
		
		List<String> notList=new ArrayList<String>();
		
		for (String string : repertoryList) {
			
			if(getRepertorys().indexOf(string)==-1){
				notList.add(string);
			}
		}
		
		
		return notList;
	}
}
